﻿using System.Reflection;
using System.Text;

namespace AVCUMPlatform.HelperLibrary
{
    public class SQLHelper
    {
        #region Public
        /// <summary>
        /// 真值
        /// </summary>
        public static string trueStr = " 1=1 ";
        /// <summary>
        /// 数据库类型
        /// </summary>
        public static string DbType = "mysql";
        /// <summary>
        /// 数据库名
        /// </summary>
        public static string Database = "";

        /// <summary>
        /// 构造插入SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="columns"></param>
        /// <param name="returnAutoId"></param>
        /// <returns></returns>
        public static string BuildInsertSQL<T>(string[] columns = null, bool returnAutoId = false)
        {
            StringBuilder sql = new StringBuilder();
            string table = GetTableName<T>();
            sql.Append($"INSERT INTO {table} ");
            sql.Append(GetColumnsSQL<T>(SQLType.INSERT, columns));
            if (returnAutoId)
            {
                sql.Append("SELECT LAST_INSERT_ID();");
            }
            if (DbType == "dm")
            {
                return sql.ToString().Replace("`", "\"").Replace("@", ":");
            }
            else if (DbType == "rdjc")
            {
                return sql.ToString().Replace("`", "\"");
            }
            else
            {
                return sql.ToString();
            }
        }

        /// <summary>
        /// 构造删除SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="conditions"></param>
        /// <param name="NAND"></param>
        /// <returns></returns>
        public static string BuildDeleteSQL<T>(string[] conditions = null, string NAND = "AND")
        {
            if (NAND == null)
            {
                NAND = "AND";
            }
            StringBuilder sql = new StringBuilder();
            string table = GetTableName<T>();
            sql.Append($"DELETE FROM {table} WHERE ");
            sql.Append(GetConditionsSQL<T>(SQLType.DELETE, conditions, NAND));
            if (DbType == "dm")
            {
                return sql.ToString().Replace("`", "\"").Replace("@", ":");
            }
            else if (DbType == "rdjc")
            {
                return sql.ToString().Replace("`", "\"");
            }
            else
            {
                return sql.ToString();
            }
        }

        /// <summary>
        /// 构造更新SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="columns"></param>
        /// <param name="conditions"></param>
        /// <param name="NAND"></param>
        /// <returns></returns>
        public static string BuildUpdateSQL<T>(string[] columns = null, string[] conditions = null, string NAND = "AND")
        {
            if (NAND == null)
            {
                NAND = "AND";
            }
            StringBuilder sql = new StringBuilder();
            string table = GetTableName<T>();
            sql.Append($"UPDATE {table} SET ");
            sql.Append(GetColumnsSQL<T>(SQLType.UPDATE, columns));
            sql.Append(" WHERE ");
            sql.Append(GetConditionsSQL<T>(SQLType.UPDATE, conditions, NAND));
            if (DbType == "dm")
            {
                return sql.ToString().Replace("`", "\"").Replace("@", ":");
            }
            else if (DbType == "rdjc")
            {
                return sql.ToString().Replace("`", "\"");
            }
            else
            {
                return sql.ToString();
            }
        }

        /// <summary>
        /// 构造查询SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="columns"></param>
        /// <param name="conditions"></param>
        /// <param name="NAND"></param>
        /// <param name="isLike"></param>
        /// <param name="orderby"></param>
        /// <param name="isdesc"></param>
        /// <returns></returns>
        public static string BuildSelectSQL<T>(string[] columns = null, string[] conditions = null, string NAND = "AND", bool isLike = false, string orderby = null, bool isdesc = false)
        {
            if (NAND == null)
            {
                NAND = "AND";
            }
            StringBuilder sql = new StringBuilder();
            string table = GetTableName<T>();
            sql.Append("SELECT ");
            sql.Append(GetColumnsSQL<T>(SQLType.SELECT, columns));
            sql.Append($" FROM {table} ");
            if (conditions != null)
            {
                sql.Append(" WHERE ");
                sql.Append(GetConditionsSQL<T>(SQLType.SELECT, conditions, NAND, isLike));
            }
            if (!string.IsNullOrWhiteSpace(orderby))
            {
                sql.Append($" ORDER BY {orderby}");
                if (isdesc)
                {
                    sql.Append(" DESC");
                }
                else
                {
                    sql.Append(" ASC");
                }
            }
            sql.Append(";");
            if (DbType == "dm")
            {
                return sql.ToString().Replace("`", "\"").Replace("@", ":");
            }
            else if (DbType == "rdjc")
            {
                return sql.ToString().Replace("`", "\"");
            }
            else
            {
                return sql.ToString();
            }
        }
        /// <summary>
        /// 查询所有Sql
        /// </summary>
        /// <returns></returns>
        public static string BuildGetByWhere<T>(string whereStr, bool limit1 = true)
        {
            Type t = typeof(T);
            string tableName = GetTableName<T>();
            var sql = "";
            if (limit1)
            {
                sql = $"SELECT * FROM {tableName} WHERE {WhereFormat(whereStr)} LIMIT 1";
            }
            else
            {
                sql = $"SELECT * FROM {tableName} WHERE {WhereFormat(whereStr)}";
            }
            if (DbType == "dm")
            {
                return sql.Replace("`", "\"").Replace("@", ":");
            }
            else if (DbType == "rdjc")
            {
                return sql.Replace("`", "\"");
            }
            else
            {
                return sql;
            }
        }
        /// <summary>
        /// 过滤特殊字符
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static object FilterSpecialChar(object obj)
        {
            FieldInfo[] infos = obj.GetType().GetFields(BindingFlags.Instance | BindingFlags.NonPublic);
            foreach (PropertyInfo propertyInfo in obj.GetType().GetProperties())
            {
                if (propertyInfo.GetValue(obj) != null)
                {
                    if (propertyInfo.GetValue(obj).GetType() == typeof(string))
                    {
                        foreach (FieldInfo fieldInfo in infos)
                        {
                            string fname = fieldInfo.Name;
                            fname = fname.Substring(fname.IndexOf("<") + 1, fname.IndexOf(">") - fname.IndexOf("<") - 1);
                            if (fname == propertyInfo.Name)
                            {
                                fieldInfo.SetValue(obj, propertyInfo.GetValue(obj).ToString().Replace("%", @"\%").Replace("_", @"\_").Replace("'", @"\'"));
                                break;
                            }
                        }
                    }
                }
            }
            return obj;
        }

        /// <summary>
        /// 过滤空列
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static object FilterNullField(object obj)
        {
            FieldInfo[] infos = obj.GetType().GetFields(BindingFlags.Instance | BindingFlags.NonPublic);
            foreach (PropertyInfo propertyInfo in obj.GetType().GetProperties())
            {
                if (propertyInfo.GetValue(obj) != null)
                {
                    if (propertyInfo.GetValue(obj).GetType() == typeof(string))
                    {
                        foreach (FieldInfo fieldInfo in infos)
                        {
                            string fname = fieldInfo.Name;
                            if (fname.Contains("<") && fname.Contains(">"))
                                fname = fname.Substring(fname.IndexOf("<") + 1, fname.IndexOf(">") - fname.IndexOf("<") - 1);
                            if (fname == propertyInfo.Name)
                            {
                                if (propertyInfo.GetValue(obj).ToString().Trim() == "")
                                {
                                    fieldInfo.SetValue(obj, null);
                                    break;
                                }
                            }
                        }
                    }
                    else if (propertyInfo.GetValue(obj).GetType() == typeof(int))
                    {
                        foreach (FieldInfo fieldInfo in infos)
                        {
                            string fname = fieldInfo.Name;
                            if (fname.Contains("<") && fname.Contains(">"))
                                fname = fname.Substring(fname.IndexOf("<") + 1, fname.IndexOf(">") - fname.IndexOf("<") - 1);
                            if (fname == propertyInfo.Name)
                            {
                                if (Convert.ToInt32(propertyInfo.GetValue(obj)) == 0)
                                {
                                    fieldInfo.SetValue(obj, null);
                                    break;
                                }
                            }
                        }
                    }
                }
            }
            return obj;
        }

        /// <summary>
        /// 构造获取总数SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <returns></returns>
        public static string BuildGetCountSql<T>(string where)
        {
            string tableName = GetTableName<T>();
            var sql = $" SELECT COUNT(1) FROM {tableName} WHERE {WhereFormat(where)} ";
            if (DbType == "dm")
            {
                return sql.Replace("`", "\"").Replace("@", ":");
            }
            else if (DbType == "rdjc")
            {
                return sql.Replace("`", "\"");
            }
            else
            {
                return sql;
            }
        }

        public static string BuildDeleteWhereSql<T>(string where)
        {
            string tableName = GetTableName<T>();
            var sql = $" DELETE FROM {tableName} WHERE {WhereFormat(where)}";
            if (DbType == "dm")
            {
                return sql.Replace("`", "\"").Replace("@", ":");
            }
            else if (DbType == "rdjc")
            {
                return sql.Replace("`", "\"");
            }
            else
            {
                return sql;
            }
        }
        /// <summary>
        /// 构造查询SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <param name="limit1"></param>
        /// <returns></returns>
        public static string BuildGetByWhereSql<T>(string where, bool limit1 = true)
        {
            string tableName = GetTableName<T>();
            var sql = "";
            if (limit1)
            {
                sql = $" SELECT * FROM {tableName} WHERE {WhereFormat(where)} LIMIT 1 ";
            }
            else
            {
                sql = $" SELECT * FROM {tableName} WHERE {WhereFormat(where)} ";
            }
            if (DbType == "dm")
            {
                return sql.Replace("`", "\"").Replace("@", ":");
            }
            else if (DbType == "rdjc")
            {
                return sql.Replace("`", "\"");
            }
            else
            {
                return sql;
            }
        }

        /// <summary>
        /// 构造删除Sql
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public static string BuildBatchDeleteSql<T>(List<int> ids)
        {
            string tableName = GetTableName<T>();
            var sql = $"DELETE FROM {tableName} WHERE `Id` IN {GetInCondition(ids)}";
            if (DbType == "dm")
            {
                return sql.Replace("`", "\"").Replace("@", ":");
            }
            else if (DbType == "rdjc")
            {
                return sql.Replace("`", "\"");
            }
            else
            {
                return sql;
            }
        }


        public static string BuildGetLast<T>(string str)
        {
            string tableName = GetTableName<T>();
            var sql = $" SELECT * FROM {tableName} ORDER BY {str} DESC LIMIT 1 ";
            if (DbType == "dm")
            {
                return sql.Replace("`", "\"").Replace("@", ":");
            }
            else if (DbType == "rdjc")
            {
                return sql.Replace("`", "\"");
            }
            else
            {
                return sql;
            }
        }
        /// <summary>
        /// 获取In 条件 ('item1', 'item2', 'item3')
        /// </summary>
        /// <param name="strList"></param>
        /// <returns></returns>
        public static string GetInCondition(List<string> strList)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("(");
            for (int i = 0; i < strList.Count; i++)
            {
                sb.Append("'");
                sb.Append(strList[i]);
                sb.Append("'");
                if (i != strList.Count - 1)
                {
                    sb.Append(",");
                }
            }
            sb.Append(")");

            return sb.ToString();
        }

        /// <summary>
        /// 获取In 条件 ('item1', 'item2', 'item3')
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public static string GetInCondition(List<int> ids)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("(");
            for (int i = 0; i < ids.Count; i++)
            {
                sb.Append(ids[i]);
                if (i != ids.Count - 1)
                {
                    sb.Append(",");
                }
            }
            sb.Append(")");

            return sb.ToString();
        }

        /// <summary>
        /// 构造单表分页Sql
        /// </summary>
        /// <returns></returns>
        public static string BuildSingleTablePageQuerySql<T>(string where, string orderByFiledName, bool isDesc, int pageIndex, int pageSize)
        {
            // 获取表名
            string tableName = GetTableName<T>();
            // 计算分页开始行号
            int start = (pageIndex - 1) * pageSize;
            // 拼接排序
            string descStr = string.Empty;
            if (isDesc)
            {
                descStr = "DESC";
            }
            string orderBy = $" ORDER BY `{orderByFiledName}` {descStr} ";
            var sql = $" SELECT * FROM {tableName} WHERE {WhereFormat(where)} {orderBy} LIMIT {start},{pageSize} ";
            if (DbType == "dm")
            {
                return sql.Replace("`", "\"").Replace("@", ":");
            }
            else if (DbType == "rdjc")
            {
                return sql.Replace("`", "\"");
            }
            else
            {
                return sql;
            }
        }

        /// <summary>
        /// 构造单表分页Sql
        /// </summary>
        /// <returns></returns>
        public static string BuildSingleTableCoutSql<T>(string where)
        {
            Type t = typeof(T);
            string tableName = GetTableName<T>();
            var sql = $" SELECT COUNT(1) FROM {tableName} WHERE {WhereFormat(where)} ";
            if (DbType == "dm")
            {
                return sql.Replace("`", "\"").Replace("@", ":");
            }
            else if (DbType == "rdjc")
            {
                return sql.Replace("`", "\"");
            }
            else
            {
                return sql;
            }
        }
        #endregion

        #region Private
        /// <summary>
        /// 获取表明
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static string GetTableName<T>()
        {
            Type t = typeof(T);
            TableAttribute[] attributes = (TableAttribute[])t.GetCustomAttributes(typeof(TableAttribute), false);
            if (attributes == null || !attributes.Any())
            {
                throw new Exception("该模型不是数据库表");
            }
            var tableName = attributes.First().Name;
            if (DbType == "dm")
            {
                return $"{Database}.\"{tableName}\"";
            }
            else if (DbType == "rdjc")
            {
                return $"{Database}.\"{tableName}\"";
            }
            else
            {
                return $"`{tableName}`";
            }
        }

        /// <summary>
        /// 获取自定义属性
        /// </summary>
        /// <param name="property"></param>
        /// <returns></returns>
        private static AttributeType GetAttributeType(PropertyInfo property)
        {
            ColumnAttribute[] columnAttributes = (ColumnAttribute[])property.GetCustomAttributes(typeof(ColumnAttribute), false);
            if (columnAttributes != null && columnAttributes.Any())
            {
                return AttributeType.Column;
            }

            PrimaryKeyAttribute[] primaryKeyAttributes = (PrimaryKeyAttribute[])property.GetCustomAttributes(typeof(PrimaryKeyAttribute), false);
            if (primaryKeyAttributes != null && primaryKeyAttributes.Any())
            {
                return AttributeType.PrimaryKey;
            }
            return AttributeType.NULL;
        }

        /// <summary>
        /// 获取属性名称
        /// </summary>
        /// <param name="property"></param>
        /// <returns></returns>
        private static string GetAttributeName(PropertyInfo property)
        {
            ColumnAttribute[] columnAttributes = (ColumnAttribute[])property.GetCustomAttributes(typeof(ColumnAttribute), false);
            if (columnAttributes != null && columnAttributes.Any())
            {
                return columnAttributes.First().Name;
            }

            PrimaryKeyAttribute[] primaryKeyAttributes = (PrimaryKeyAttribute[])property.GetCustomAttributes(typeof(PrimaryKeyAttribute), false);
            if (primaryKeyAttributes != null && primaryKeyAttributes.Any())
            {
                return primaryKeyAttributes.First().Name;
            }
            return property.Name;
        }

        /// <summary>
        /// 获取列SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="type"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        private static string GetColumnsSQL<T>(SQLType type, string[] columns = null)
        {
            StringBuilder sql = new StringBuilder();
            switch (type)
            {
                case SQLType.INSERT:
                    if (columns == null)
                    {
                        Type t = typeof(T);
                        string nameStr = string.Empty;
                        string valuesStr = string.Empty;
                        foreach (PropertyInfo item in t.GetProperties())
                        {
                            if (GetAttributeType(item) == AttributeType.Column)
                            {
                                string column = GetAttributeName(item);
                                nameStr += $"`{column}`,";
                                valuesStr += $"@{column},";
                            }
                        }
                        nameStr = nameStr.Substring(0, nameStr.Length - 1);
                        valuesStr = valuesStr.Substring(0, valuesStr.Length - 1);
                        sql.Append($"({nameStr}) VALUES (");
                        sql.Append($"{valuesStr});");
                    }
                    else
                    {
                        string nameStr = string.Empty;
                        string valuesStr = string.Empty;
                        foreach (string column in columns)
                        {
                            if (!column.Contains("`"))
                            {
                                nameStr += $"`{column}`,";
                            }
                            else
                            {
                                nameStr += $"{column},";
                            }
                            valuesStr += $"@{column},";
                        }
                        nameStr = nameStr.Substring(0, nameStr.Length - 1);
                        valuesStr = valuesStr.Substring(0, valuesStr.Length - 1);
                        sql.Append($"({nameStr})VALUES(");
                        sql.Append($"{valuesStr});");
                    }
                    break;
                case SQLType.DELETE:
                    break;
                case SQLType.UPDATE:
                    if (columns == null)
                    {
                        Type t = typeof(T);
                        string setStr = string.Empty;
                        foreach (PropertyInfo item in t.GetProperties())
                        {
                            if (GetAttributeType(item) == AttributeType.Column)
                            {
                                string column = GetAttributeName(item);
                                setStr += $"`{column}`=@{column},";
                            }
                        }
                        setStr = setStr.Substring(0, setStr.Length - 1);
                        sql.Append(setStr);
                    }
                    else
                    {
                        string setStr = string.Empty;
                        foreach (string column in columns)
                        {
                            if (!column.Contains("`"))
                            {
                                setStr += $"`{column}`=@{column},";
                            }
                            else
                            {
                                setStr += $"{column}=@{column},";
                            }
                        }
                        setStr = setStr.Substring(0, setStr.Length - 1);
                        sql.Append(setStr);
                    }
                    break;
                case SQLType.SELECT:
                    if (columns == null)
                        sql.Append("*");
                    else
                    {
                        string selectStr = string.Empty;
                        foreach (string column in columns)
                        {
                            if (!column.Contains("`"))
                            {
                                selectStr += $"`{column}`,";
                            }
                            else
                            {
                                selectStr += $"{column},";
                            }
                        }
                        selectStr = selectStr.Substring(0, selectStr.Length - 1);
                        sql.Append(selectStr);
                    }
                    break;
            }
            return sql.ToString();
        }

        /// <summary>
        /// 获取查询条件SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="type"></param>
        /// <param name="conditions"></param>
        /// <param name="NAND"></param>
        /// <param name="isLike"></param>
        /// <returns></returns>
        private static string GetConditionsSQL<T>(SQLType type, string[] conditions = null, string NAND = "AND", bool isLike = false)
        {
            StringBuilder sql = new StringBuilder();
            switch (type)
            {
                case SQLType.INSERT:
                    break;
                case SQLType.DELETE:
                case SQLType.UPDATE:
                    if (conditions == null)
                    {
                        string primaryKey = GetPrimaryKey<T>();
                        sql.Append($" {primaryKey}=@{primaryKey}; ");
                    }
                    else
                    {
                        for (int i = 0; i < conditions.Length; i++)
                        {
                            sql.Append($" {conditions[i]}=@{conditions[i]} ");
                            if (i != conditions.Length - 1)
                            {
                                sql.Append($" {NAND} ");
                            }
                        }
                        sql.Append(";");
                    }
                    break;
                case SQLType.SELECT:
                    for (int i = 0; i < conditions.Length; i++)
                    {
                        if (isLike)
                            sql.Append($" {conditions[i]} like concat('%',@{conditions[i]},'%') ");
                        else
                            sql.Append($" {conditions[i]}=@{conditions[i]} ");
                        if (i != conditions.Length - 1)
                            sql.Append($" {NAND} ");
                    }
                    break;
            }
            return sql.ToString();
        }

        /// <summary>
        /// 获取主键
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        private static string GetPrimaryKey<T>()
        {
            string primaryKey = null;
            Type t = typeof(T);
            foreach (PropertyInfo item in t.GetProperties())
            {
                if (GetAttributeType(item) == AttributeType.PrimaryKey)
                {
                    primaryKey = GetAttributeName(item);
                    break;
                }
            }
            if (primaryKey == null)
                throw new Exception("未找到主键");
            return primaryKey;
        }

        /// <summary>
        /// where 条件格式化
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        private static string WhereFormat(string where)
        {
            if (string.IsNullOrEmpty(where))
            {
                where = "1=1";
            }
            else
            {
                where = where.TrimStart();
                if (where.ToUpper().IndexOf("WHERE") == 0)
                {
                    where = where.Substring(5);
                }
            }
            return where;
        }

        /// <summary>
        /// 构建批量插入语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public static string BuildRealBatchInsertSql<T>(List<T> list)
        {
            Type t = typeof(T);
            string tableName = GetTableName<T>();
            StringBuilder sb = new StringBuilder();
            sb.Append($"INSERT INTO {tableName} (");
            bool isFirst = true;

            PropertyInfo[] properties = t.GetProperties();
            foreach (PropertyInfo p in properties)
            {
                if (p.Name.ToUpper() == "ID")
                {
                    continue;
                }
                if (isFirst)
                {
                    isFirst = false;
                    sb.Append($"`{p.Name}`");
                }
                else
                {
                    sb.Append($",`{p.Name}`");
                }
            }

            sb.Append(")VALUES");

            foreach (T item in list)
            {
                sb.Append("(");
                isFirst = true;
                foreach (PropertyInfo p in properties)
                {
                    if (p.Name.ToUpper() == "ID")
                    {
                        continue;
                    }
                    object o = t.GetProperty(p.Name).GetValue(item, null);
                    if (isFirst)
                    {
                        if (o == null)
                        {
                            sb.Append("NULL");
                        }
                        else
                        {
                            string value = Convert.ToString(o);
                            if (o.GetType() == typeof(bool))
                            {
                                sb.Append($"{value.ToUpper()}");
                            }
                            else if ((o.GetType() == typeof(DateTime)))
                            {
                                sb.Append($"'{((DateTime)o).ToYMDHMS()}'");
                            }
                            else
                            {
                                sb.Append($"'{value}'");
                            }
                        }
                        isFirst = false;
                    }
                    else
                    {
                        if (o == null)
                        {
                            sb.Append(",NULL");
                        }
                        else
                        {
                            string value = Convert.ToString(o);
                            if (o.GetType() == typeof(bool))
                            {
                                sb.Append($",{value.ToUpper()}");
                            }
                            else if ((o.GetType() == typeof(DateTime)))
                            {
                                sb.Append($",'{((DateTime)o).ToYMDHMS()}'");
                            }
                            else
                            {
                                sb.Append($",'{value}'");
                            }
                        }
                    }
                }

                sb.Append("),");
            }

            var sql = sb.ToString().TrimEnd(',');
            if (DbType == "dm")
            {
                return sql.Replace("`", "\"").Replace("@", ":");
            }
            else if (DbType == "rdjc")
            {
                return sql.Replace("`", "\"");
            }
            else
            {
                return sql;
            }
        }

        /// <summary>
        /// 构建批量插入语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public static string BuildBatchUpdateSql<T>(List<T> list)
        {
            Type t = typeof(T);
            string tableName = GetTableName<T>();
            List<string> majorPropertyNames = new List<string>() { "ID" };
            List<PropertyInfo> properties = t.GetProperties().ToList();
            PropertyInfo marjorProperty = properties
                .First(p => majorPropertyNames.Contains(p.Name.ToUpper()));
            StringBuilder sb = new StringBuilder();
            foreach (T item in list)
            {
                bool isFirst = true;
                sb.Append($" UPDATE {tableName} SET ");
                foreach (PropertyInfo p in properties)
                {
                    if (majorPropertyNames.Contains(p.Name.ToUpper()))
                    {
                        continue;
                    }

                    object o = t.GetProperty(p.Name).GetValue(item, null);
                    if (isFirst)
                    {
                        isFirst = false;
                        if (o == null)
                        {
                            sb.Append($"`{p.Name}` = NULL");
                        }
                        else
                        {
                            string value = Convert.ToString(o);
                            if (o.GetType() == typeof(bool))
                            {
                                sb.Append($"`{p.Name}` = {value.ToUpper()}");
                            }
                            else if (o.GetType() == typeof(DateTime))
                            {
                                value = ((DateTime)o).ToYMDHMS();
                                sb.Append($"`{p.Name}` = '{value}'");
                            }
                            else
                            {
                                sb.Append($"`{p.Name}` = '{value}'");
                            }
                        }
                    }
                    else
                    {
                        if (o == null)
                        {
                            sb.Append($", `{p.Name}` = NULL");
                        }
                        else if (o.GetType() == typeof(DateTime))
                        {
                            var value = ((DateTime)o).ToYMDHMS();
                            sb.Append($", `{p.Name}` = '{value}'");
                        }
                        else
                        {
                            string value = Convert.ToString(o);
                            if (o.GetType() == typeof(bool))
                            {
                                sb.Append($", `{p.Name}` = {value.ToUpper()}");
                            }
                            else
                            {
                                sb.Append($", `{p.Name}` = '{value}'");
                            }
                        }
                    }
                }

                string majorKeyVal = t.GetProperty(marjorProperty.Name).GetValue(item, null).ToString();
                sb.Append($" WHERE Id = {majorKeyVal};");
            }

            var sql = sb.ToString();
            if (DbType == "dm")
            {
                return sql.Replace("`", "\"").Replace("@", ":");
            }
            else if (DbType == "rdjc")
            {
                return sql.Replace("`", "\"");
            }
            else
            {
                return sql;
            }
        }
        /// <summary>
        /// 单引号处理
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static string SpecialCharacterConvert(string str)
        {
            if (string.IsNullOrEmpty(str))
            {
                return str;
            }
            if (str.Contains(@"\"))
            {
                str = str.Replace(@"\", @"\\'");
            }
            if (str.Contains("'"))
            {
                str = str.Replace("'", @"\'");
            }
            return str;
        }

        #endregion
    }

    public enum SQLType
    {
        INSERT,
        DELETE,
        UPDATE,
        SELECT
    }
}
